﻿using Bnbjoy.Domain.Abstract;
using Bnbjoy.Domain.Common;
using Bnbjoy.Domain.Entities;
using MySql.Data.MySqlClient;
using Newtonsoft.Json;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.Entity.Core;
using System.Data.Entity.Validation;
using System.Dynamic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Bnbjoy.Domain.Concrete
{
    public class RoomTypeRepository : IRoomTypeRepository
    {
        public async Task<dynamic> FindRoomType(string bnbId, string roomTypeName)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    string sql = "select rt.Rank as RoomTypeRank, rt.RoomTypeId, rt.RoomTypeName, rt.Notes, rt.BnbId, " +
    "cp.Id as CommonPriceId, cp.InitPrice, cp.MondayPrice, cp.TuesdayPrice, cp.WednesdayPrice, cp.ThursdayPrice, cp.FridayPrice, cp.SaturdayPrice, cp.SundayPrice, " +
    "r.Rank as RoomRank, r.RoomId, r.RoomNumber, r.Enabled " +
    "from RoomType rt inner join CommonPrice cp on rt.RoomTypeId = cp.RoomTypeId " +
    "inner join Room r on r.RoomTypeId = rt.RoomTypeId " +
    "where rt.BnbId = @param1 and rt.RoomTypeName = @param2";
                    dynamic results = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", bnbId), new MySqlParameter("param2", roomTypeName));
                    string resultJson = JsonConvert.SerializeObject(results);
                    List<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);

                    var aggregate = list.GroupBy(x => x.RoomTypeId).Select(group => new
                    {
                        RoomTypeInfo = group.Select(n => new
                        {
                            RoomTypeRank = n.RoomTypeRank,
                            RoomTypeId = n.RoomTypeId,
                            RoomTypeName = n.RoomTypeName,
                            Notes = n.Notes,
                            BnbId = n.BnbId,
                            CommonPriceId = n.CommonPriceId,
                            InitPrice = n.InitPrice,
                            MondayPrice = n.MondayPrice,
                            TuesdayPrice = n.TuesdayPrice,
                            WednesdayPrice = n.WednesdayPrice,
                            ThursdayPrice = n.ThursdayPrice,
                            FridayPrice = n.FridayPrice,
                            SaturdayPrice = n.SaturdayPrice,
                            SundayPrice = n.SundayPrice
                        }).First(),
                        Rooms = group.Select(n => new
                        {
                            RoomRank = n.RoomRank,
                            RoomId = n.RoomId,
                            RoomNumber = n.RoomNumber,
                            Enabled = n.Enabled
                        }).ToList()
                    }).FirstOrDefault();

                    if (aggregate != null)
                    {
                        dynamic result = new ExpandoObject();

                        result.RoomTypeId = aggregate.RoomTypeInfo.RoomTypeId;
                        result.RoomTypeRank = aggregate.RoomTypeInfo.RoomTypeRank;
                        result.RoomTypeName = aggregate.RoomTypeInfo.RoomTypeName;
                        result.Notes = aggregate.RoomTypeInfo.Notes;
                        result.BnbId = aggregate.RoomTypeInfo.BnbId;
                        result.CommonPriceId = aggregate.RoomTypeInfo.CommonPriceId;
                        result.InitPrice = aggregate.RoomTypeInfo.InitPrice;
                        result.MondayPrice = aggregate.RoomTypeInfo.MondayPrice;
                        result.TuesdayPrice = aggregate.RoomTypeInfo.TuesdayPrice;
                        result.WednesdayPrice = aggregate.RoomTypeInfo.WednesdayPrice;
                        result.ThursdayPrice = aggregate.RoomTypeInfo.ThursdayPrice;
                        result.FridayPrice = aggregate.RoomTypeInfo.FridayPrice;
                        result.SaturdayPrice = aggregate.RoomTypeInfo.SaturdayPrice;
                        result.SundayPrice = aggregate.RoomTypeInfo.SundayPrice;

                        result.Rooms = aggregate.Rooms;
                        return result;
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public async Task<IEnumerable<dynamic>> ListAllRoomTypes(string bnbId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    string sql = "select rt.Rank as RoomTypeRank, rt.RoomTypeId, rt.RoomTypeName, rt.Notes, rt.BnbId, " +
    "cp.Id as CommonPriceId, cp.InitPrice, cp.MondayPrice, cp.TuesdayPrice, cp.WednesdayPrice, cp.ThursdayPrice, cp.FridayPrice, cp.SaturdayPrice, cp.SundayPrice, " +
    "r.Rank as RoomRank, r.RoomId, r.RoomNumber, r.Enabled " +
    "from RoomType rt inner join CommonPrice cp on rt.RoomTypeId = cp.RoomTypeId " +
    "inner join Room r on r.RoomTypeId = rt.RoomTypeId " +
    "where rt.BnbId = @param1 order by rt.Rank asc";
                    dynamic results = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", bnbId));
                    string resultJson = JsonConvert.SerializeObject(results);
                    List<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);

                    var aggregate = list.GroupBy(x => x.RoomTypeId).Select(group => new
                    {
                        RoomTypeInfo = group.Select(n => new
                        {
                            RoomTypeRank = n.RoomTypeRank,
                            RoomTypeId = n.RoomTypeId,
                            RoomTypeName = n.RoomTypeName,
                            Notes = n.Notes,
                            BnbId = n.BnbId,
                            CommonPriceId = n.CommonPriceId,
                            InitPrice = n.InitPrice,
                            MondayPrice = n.MondayPrice,
                            TuesdayPrice = n.TuesdayPrice,
                            WednesdayPrice = n.WednesdayPrice,
                            ThursdayPrice = n.ThursdayPrice,
                            FridayPrice = n.FridayPrice,
                            SaturdayPrice = n.SaturdayPrice,
                            SundayPrice = n.SundayPrice
                        }).First(),
                        Rooms = group.Select(n => new
                        {
                            RoomRank = n.RoomRank,
                            RoomId = n.RoomId,
                            RoomNumber = n.RoomNumber,
                            Enabled = n.Enabled
                        }).ToList()
                    }).ToList();

                    List<dynamic> bResult = new List<dynamic>();
                    if (aggregate != null && aggregate.Count() > 0)
                    {
                        foreach (var roomType in aggregate)
                        {
                            dynamic temp = new ExpandoObject();
                            temp.RoomTypeId = roomType.RoomTypeInfo.RoomTypeId;
                            temp.RoomTypeRank = roomType.RoomTypeInfo.RoomTypeRank;
                            temp.RoomTypeName = roomType.RoomTypeInfo.RoomTypeName;
                            temp.Notes = roomType.RoomTypeInfo.Notes;
                            temp.BnbId = roomType.RoomTypeInfo.BnbId;
                            temp.CommonPriceId = roomType.RoomTypeInfo.CommonPriceId;
                            temp.InitPrice = roomType.RoomTypeInfo.InitPrice;
                            temp.MondayPrice = roomType.RoomTypeInfo.MondayPrice;
                            temp.TuesdayPrice = roomType.RoomTypeInfo.TuesdayPrice;
                            temp.WednesdayPrice = roomType.RoomTypeInfo.WednesdayPrice;
                            temp.ThursdayPrice = roomType.RoomTypeInfo.ThursdayPrice;
                            temp.FridayPrice = roomType.RoomTypeInfo.FridayPrice;
                            temp.SaturdayPrice = roomType.RoomTypeInfo.SaturdayPrice;
                            temp.SundayPrice = roomType.RoomTypeInfo.SundayPrice;

                            temp.Rooms = roomType.Rooms;
                            bResult.Add(temp);
                        }
                        return bResult;
                    }
                    else
                    {
                        return null;
                    }
                }
                catch (Exception ex)
                {
                    return null;
                }
            }
        }

        public async Task<bool> InsertRoomType(RoomType roomType, IEnumerable<Room> rooms, CommonPrice commonPrice)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                using (var transaction = bnbjoyBackendDbEntities.Database.BeginTransaction())
                {
                    try
                    {
                        bool insertRoomType = false, insertRooms = false, insertCommonPrice = false;
                        bool existedRoomType = await bnbjoyBackendDbEntities.RoomTypes.AsNoTracking().Where(r => r.RoomTypeName == roomType.RoomTypeName && r.BnbId == roomType.BnbId).AnyAsync();
                        if (!existedRoomType)
                        {
                            bnbjoyBackendDbEntities.RoomTypes.Add(roomType);
                            insertRoomType = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }

                        //去掉重复值(通过UI提交过来的房号可能存在重复)
                        rooms = rooms.GroupBy(x => x.RoomNumber).Select(y => y.First());
                        //同一个bnb的所有房间不能含有同名的，否则无法插入
                        var roomNumbers = rooms.Select(i => i.RoomNumber);
                        var existedRooms = await bnbjoyBackendDbEntities.Rooms.Where(r => roomNumbers.Contains(r.RoomNumber)).ToListAsync();

                        //var existedRooms = await bnbjoyBackendDbEntities.Rooms.AsNoTracking().Where(r => rooms.Select(room => room.RoomNumber).Contains(r.RoomNumber)).ToListAsync();
                        if (existedRooms == null || existedRooms.Count < 1)
                        {
                            bnbjoyBackendDbEntities.Rooms.AddRange(rooms);
                            insertRooms = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }

                        //一个房型对应一个common price，如果已存在关联，则无法插入(但是在实际注册中，新建立的房型几乎不会有对应的Comon Price)
                        //var existedCommonPrice = await bnbjoyBackendDbEntities.CommonPrices.Where(cp => cp.RoomTypeId == roomType.RoomTypeId).AnyAsync();
                        bnbjoyBackendDbEntities.CommonPrices.Add(commonPrice);
                        insertCommonPrice = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                        if (insertRoomType && insertRooms && insertCommonPrice)
                        {
                            transaction.Commit();
                        }
                        //当房型，房间，默认价格都插入完成新建才算成功
                        return insertRoomType && insertRooms && insertCommonPrice;
                    }
                    catch (DbEntityValidationException e)
                    {
                        foreach (var eve in e.EntityValidationErrors)
                        {
                            Console.WriteLine("Entity of type \"{0}\" in state \"{1}\" has the following validation errors:",
                                eve.Entry.Entity.GetType().Name, eve.Entry.State);
                            foreach (var ve in eve.ValidationErrors)
                            {
                                Console.WriteLine("- Property: \"{0}\", Error: \"{1}\"",
                                    ve.PropertyName, ve.ErrorMessage);
                            }
                        }
                        throw;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }

        public async Task<bool> UpRank(string bnbId, int currentRank)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select max(rank) as PreviousRank from RoomType where Rank < @param1 and BnbId = @param2 limit 1";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", currentRank), new MySqlParameter("param2", bnbId));
                string resultJson = JsonConvert.SerializeObject(data);
                List<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);
                int previousRank = list.First().PreviousRank;
                bool result = await ExchangeTwoRoomType(previousRank, currentRank);
                return result;
            }
        }

        public async Task<bool> DownRank(string bnbId, int currentRank)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select min(rank) as AfterRank from RoomType where Rank > @param1 and BnbId = @param2 limit 1";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", currentRank), new MySqlParameter("param2", bnbId));
                string resultJson = JsonConvert.SerializeObject(data);
                List<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);
                int afterRank = list.First().AfterRank;
                bool result = await ExchangeTwoRoomType(afterRank, currentRank);
                return result;
            }
        }

        private async Task<bool> ExchangeTwoRoomType(int index1, int index2)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                try
                {
                    //根据rank交换两个房型的sql语句
                    string sql = "update RoomType a inner join RoomType b on a.Rank <> b.Rank " +
                                   "set a.RoomTypeId = b.RoomTypeId, " +
                                       "a.RoomTypeName = b.RoomTypeName, " +
                                       "a.Notes = b.Notes " +
                                 "where a.Rank in (@param1,@param2) and b.Rank in (@param1,@param2)";
                    int result = await bnbjoyBackendDbEntities.Database.ExecuteSqlCommandAsync(sql, new MySqlParameter("param1", index1), new MySqlParameter("param2", index2));
                    return result > 0;
                }
                catch (Exception ex)
                {
                    return false;
                }
            }
        }

        public async Task<bool> UpdateDailyPrice(DateTime fromDate, DateTime toDate, string roomTypeId, decimal price)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                using (var transaction = bnbjoyBackendDbEntities.Database.BeginTransaction())
                {
                    try
                    {
                        bool success = false;
                        DateTime fstart = fromDate;
                        DateTime fend = toDate;

                        //找出该房型与待出入数据存在时间重合的记录
                        string sql = "SELECT * FROM DAILYPRICE WHERE FROMDATE <=@param2  AND TODATE >=@param1 and RoomTypeId = @param3 order by Id";
                        dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", fromDate), new MySqlParameter("param2", toDate), new MySqlParameter("param3", roomTypeId));
                        string resultJson = JsonConvert.SerializeObject(data);
                        List<DailyPrice> overlaps = JsonConvert.DeserializeObject<List<DailyPrice>>(resultJson);
                        List<DailyPrice> fullOverlaps = overlaps.Where(dp => dp.FromDate >= fstart && dp.ToDate <= fend).ToList();
                        List<DailyPrice> partOverlaps = new List<DailyPrice>();

                        //得到不完全重合的时间段
                        foreach (var o in overlaps)
                        {
                            if (!fullOverlaps.Contains(o))
                            {
                                partOverlaps.Add(o);
                            }
                        }

                        //先删掉完全重合的
                        if (fullOverlaps.Count > 0)
                        {
                            fullOverlaps.ForEach(f =>
                            {
                                bnbjoyBackendDbEntities.DailyPrices.Attach(f);
                            });

                            bnbjoyBackendDbEntities.DailyPrices.RemoveRange(fullOverlaps);
                            success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }

                        //进行下一步分析
                        if (partOverlaps.Count == 0) //除去完全重合部分，再无任何重合
                        {
                            //直接插入
                            DailyPrice dp = new DailyPrice
                            {
                                FromDate = fstart,
                                ToDate = fend,
                                RoomTypeId = roomTypeId,
                                Price = price
                            };
                            bnbjoyBackendDbEntities.DailyPrices.Add(dp);
                            success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }
                        else if (partOverlaps.Count == 1) //除去完全重合部分，只有1个不完全重合
                        {
                            #region
                            var overlap = partOverlaps.First();

                            //如果存在一个范围，起始时间<fstart，结束时间>fend
                            if (overlap.FromDate < fstart && overlap.ToDate > fend)
                            {
                                DailyPrice dp = new DailyPrice
                                {
                                    FromDate = fstart,
                                    ToDate = fend,
                                    RoomTypeId = roomTypeId,
                                    Price = price
                                };

                                DailyPrice dp1 = new DailyPrice
                                {
                                    FromDate = fend.AddDays(1),
                                    ToDate = overlap.ToDate,
                                    RoomTypeId = roomTypeId,
                                    Price = overlap.Price
                                };

                                bnbjoyBackendDbEntities.DailyPrices.AddRange(new DailyPrice[] { dp, dp1 });
                                success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                                bnbjoyBackendDbEntities.DailyPrices.Attach(overlap);
                                overlap.ToDate = fstart.AddDays(-1);
                                success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                            }

                            //如果存在一个范围，起始时间 < fstart，结束时间 > fstart < fend
                            if (overlap.FromDate <= fstart && overlap.ToDate >= fstart && overlap.ToDate <= fend)
                            {
                                bnbjoyBackendDbEntities.DailyPrices.Attach(overlap);
                                overlap.ToDate = fstart.AddDays(-1);
                                success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                                DailyPrice dp = new DailyPrice
                                {
                                    FromDate = fstart,
                                    ToDate = fend,
                                    RoomTypeId = roomTypeId,
                                    Price = price
                                };
                                bnbjoyBackendDbEntities.DailyPrices.Add(dp);
                                success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                            }

                            //如果存在一个范围，起始时间 > fstart && 起始时间 < fend && 结束时间 > fend
                            if (overlap.FromDate >= fstart && overlap.FromDate <= fend && overlap.ToDate >= fend)
                            {
                                bnbjoyBackendDbEntities.DailyPrices.Attach(overlap);
                                overlap.FromDate = fend.AddDays(1);
                                success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;

                                DailyPrice dp = new DailyPrice
                                {
                                    FromDate = fstart,
                                    ToDate = fend,
                                    RoomTypeId = roomTypeId,
                                    Price = price
                                };
                                bnbjoyBackendDbEntities.DailyPrices.Add(dp);
                                success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                            }
                            #endregion
                        }
                        else if (partOverlaps.Count == 2)
                        {
                            foreach (var overlap in partOverlaps)
                            {
                                //如果存在一个范围，起始时间 < fstart，结束时间 > fstart < fend
                                if (overlap.FromDate <= fstart && overlap.ToDate >= fstart && overlap.ToDate <= fend)
                                {
                                    bnbjoyBackendDbEntities.DailyPrices.Attach(overlap);
                                    overlap.ToDate = fstart.AddDays(-1);
                                    success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                                }

                                //如果存在一个范围，起始时间 > fstart && 起始时间 < fend && 结束时间 > fend
                                if (overlap.FromDate >= fstart && overlap.FromDate <= fend && overlap.ToDate >= fend)
                                {
                                    bnbjoyBackendDbEntities.DailyPrices.Attach(overlap);
                                    overlap.FromDate = fend.AddDays(1);
                                    success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                                }
                            }

                            DailyPrice dp = new DailyPrice
                            {
                                FromDate = fstart,
                                ToDate = fend,
                                RoomTypeId = roomTypeId,
                                Price = price
                            };
                            bnbjoyBackendDbEntities.DailyPrices.Add(dp);
                            success = await bnbjoyBackendDbEntities.SaveChangesAsync() > 0;
                        }

                        if (success)
                            transaction.Commit();
                        return success;
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback();
                        return false;
                    }
                }
            }
        }

        public async Task<List<DailyPrice>> ListDailyPrice(DateTime dateTime, string roomTypeId)
        {
            string minDate = dateTime.ToString("yyyy-MM-dd");
            string maxDate = dateTime.AddMonths(1).ToString("yyyy-MM-dd");
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select * from DailyPrice where roomTypeId = @param1 and fromDate >= @param2 and toDate < @param3 order by Id";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", roomTypeId), new MySqlParameter("param2", minDate), new MySqlParameter("param3", maxDate));
                string resultJson = JsonConvert.SerializeObject(data);
                List<DailyPrice> dailyPriceList = JsonConvert.DeserializeObject<List<DailyPrice>>(resultJson);
                return dailyPriceList;
            }
        }

        public async Task<List<DailyPrice>> ListDailyPrice(DateTime fromTime, DateTime toTime, string roomTypeId)
        {
            string minDate = fromTime.ToString("yyyy-MM-dd");
            string maxDate = toTime.ToString("yyyy-MM-dd");
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select * from DailyPrice where roomTypeId = @param1 and toDate >= @param2 and fromDate < @param3 order by Id";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", roomTypeId), new MySqlParameter("param2", minDate), new MySqlParameter("param3", maxDate));
                string resultJson = JsonConvert.SerializeObject(data);
                List<DailyPrice> dailyPriceList = JsonConvert.DeserializeObject<List<DailyPrice>>(resultJson);
                return dailyPriceList;
            }
        }

        public async Task<CommonPrice> RetrieveCommonPrice(string roomTypeId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select * from CommonPrice where roomTypeId = @param1";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", roomTypeId));
                string resultJson = JsonConvert.SerializeObject(data);
                List<CommonPrice> commonPrices = JsonConvert.DeserializeObject<List<CommonPrice>>(resultJson);
                return commonPrices.FirstOrDefault();
            }
        }

        public async Task<List<CommonPrice>> RetrieveCommonPriceByBnbId(string bnbId)
        {
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select c.Id, c.InitPrice, c.MondayPrice, c.TuesdayPrice, c.WednesdayPrice, c.ThursdayPrice, c.FridayPrice, c.SaturdayPrice, c.SundayPrice, c.RoomTypeId, rt.RoomTypeName from CommonPrice c inner join RoomType rt on  c.RoomTypeId = rt.RoomTypeId where rt.BnbId = @param1";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", bnbId));
                string resultJson = JsonConvert.SerializeObject(data);
                List<CommonPrice> commonPrices = JsonConvert.DeserializeObject<List<CommonPrice>>(resultJson);
                return commonPrices.ToList();
            }
        }

        public async Task<List<DailyPrice>> ListDailyPriceByBnbId(DateTime fromTime, DateTime toTime, string BnbId)
        {
            string minDate = fromTime.ToString("yyyy-MM-dd");
            string maxDate = toTime.ToString("yyyy-MM-dd");
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select d.Id, d.FromDate, d.ToDate, d.Price, d.RoomTypeId from DailyPrice d inner join RoomType rt where rt.roomTypeId = d.RoomTypeId and toDate >= @param1 and fromDate < @param2 and bnbId = @param3 order by rt.Rank";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", minDate), new MySqlParameter("param2", maxDate), new MySqlParameter("param3", BnbId));
                string resultJson = JsonConvert.SerializeObject(data);
                List<DailyPrice> dailyPriceList = JsonConvert.DeserializeObject<List<DailyPrice>>(resultJson);
                return dailyPriceList;
            }
        }

        public async Task<List<dynamic>> ListBasicRoomsInfo(DateTime fromTime, DateTime toTime, string bnbId)
        {
            string minDate = fromTime.ToString("yyyy-MM-dd");
            string maxDate = toTime.ToString("yyyy-MM-dd");
            using (BnbjoyBackendDbEntities bnbjoyBackendDbEntities = new BnbjoyBackendDbEntities())
            {
                string sql = "select r.RoomId, r.RoomNumber, r.RoomTypeId, rt.RoomTypeName, o.OrderId, o.FromDate, o.ToDate, o.RoomStatus,  o.ReserverUser from (Room r LEFT JOIN `Order` o on r.RoomId = o.RoomId) inner join RoomType rt on r.RoomTypeId = rt.RoomTypeId  where toDate >= @param1 and fromDate < @param2 and rt.bnbId = @param3 order by rt.RoomTypeId";
                dynamic data = bnbjoyBackendDbEntities.Database.DynamicSqlQuery(sql, new MySqlParameter("param1", minDate), new MySqlParameter("param2", maxDate), new MySqlParameter("param3", bnbId));
                string resultJson = JsonConvert.SerializeObject(data);
                List<dynamic> list = JsonConvert.DeserializeObject<List<dynamic>>(resultJson);

                //var aggregate = list.GroupBy(x => x.RoomId).Select(group => new
                //{
                //    RoomInfo = group.Select(n => new
                //    {
                //        RoomId = n.RoomId,
                //        RoomNumber = n.RoomNumber,
                //        RoomTypeId = n.RoomTypeId,
                //        RoomTypeName = n.RoomTypeName,
                //    }).First(),
                //    Orders = group.Select(n => new
                //    {
                //        OrderId = n.OrderId,
                //        FromDate = n.FromDate,
                //        ToDate = n.ToDate,
                //        RoomStatus = n.RoomStatus,
                //        ReserverUser = n.ReserverUser,
                //        RoomId = n.RoomId
                //    }).ToList()
                //}).FirstOrDefault();

                //if (aggregate != null)
                //{
                //    dynamic result = new ExpandoObject();
                //    result.RoomId = aggregate.RoomInfo.RoomId;
                //    result.RoomNumber = aggregate.RoomInfo.RoomNumber;
                //    result.RoomTypeId = aggregate.RoomInfo.RoomTypeId;
                //    result.RoomTypeName = aggregate.RoomInfo.RoomTypeName;

                //    result.Orders = aggregate.Orders;
                //    return result;
                //}
                //else
                //{
                //    return null;
                //}

                return list;
            }
        }
    }
}
